In QlikView 9, How to report on what Support Cases were open on any given date using open date and close date?

So here is what I have in the most basic terms:
1. A Calendar (see my previous post)
My new and improved Calendar in QlikView 9

2. A table loaded from a Ticketing/Case Management database (in this instance it is Salesforce but if could be any ticketing system).

Load CaseId as CaseNumber,
	CreatedDate as DateOpened
SQL SELECT CaseId,
	CreatedDate as DateOpened,
	ClosedDate as DateClosed,
FROM Case;

The table loaded would look like this (only I would have exact times to the second for open and closed dates:

So each of these cases have a range of time they were open.
For example, Case 0001 was opened on 10/2/09. It continues to be open at the start of the day on 10/3/09, 10/4/09, and 10/5/09 before it was closed later in the day on 10/5/09.

So I have a MasterCalendar that has every day in it. I want to click on 10/4/09 and have this case show as being opened on that day.

I will be getting this to work this week, I hope. So stay in touch.

….

I am back, and much faster than I thought I would be.

Ok, so you won’t have my Salesforce database, so I created an inline load of data you can use to see this work.

Cases:
LOAD * INLINE [
CaseNumber,	DateOpened,	DateClosed
	0001,	11/2/09,	11/5/09
	0002,	11/2/09,	11/12/09
	0003,	11/3/09,	11/4/09
	0004,	11/3/09,	11/9/09
	0005,	11/3/09,	11/1/09
	0006,	11/4/09,	11/4/09
	0007,	11/4/09,	11/3/09
	0008,	11/4/09,	11/12/09
	0009,	11/4/09,	11/4/09
	0010,	11/5/09,	11/9/09
	0011,	11/5/09,	11/6/09
	0012,	11/5/09,	11/6/09
	0013,	11/6/09,	11/6/09
	0014,	11/6/09,	11/9/09
	0015,	11/6/09,	11/9/09
	0016,	11/9/09,	11/11/09
	0017,	11/9/09,	11/21/09
	0018,	11/10/09,	11/13/09
	0019,	11/10/09,	11/13/09
	0020,	11/11/09,	11/21/09
	0021,	11/12/09,	11/31/09
	0022,	11/13/09,	11/115/09
	0023,	11/16/09,	11/17/09
	0024,	11/16/09,	11/31/09
	0025,	11/17/09,	11/31/09
	0026,	11/17/09,	11/31/09
	0027,	11/17/09,	11/25/09
	0028,	11/18/09,	11/23/09
	0029,	11/19/09,	11/27/09
	0030,	11/20/09,	11/21/09
	0031,	11/23/09,	11/23/09
	0032,	11/23/09,	11/23/09
	0033,	11/24/09,	11/25/09
	0034,	11/24/09,	11/26/09
	0035,	11/25/09,	11/29/09
	0036,	11/25/09,	11/31/09
	0037,	11/26/09,	11/30/09
	0038,	11/27/09,	11/31/09
	0039,	11/27/09,	11/30/09
	0040,	11/30/09,	11/31/09
	0041,	11/30/09,	11/31/09
	0042,	11/30/09,	11/31/09
	0043,	11/31/09,	11/31/09
	0044,	11/31/09,	12/1/09
	0045,	11/31/09,	12/1/09
];

Now just do this, and you have your information that you need.

LEFT JOIN ([MasterCalendar])
INTERVALMATCH (CalendarDate)
LOAD
	DateOpened,
	DateClosed
RESIDENT [Cases];

LEFT JOIN ([MasterCalendar])
LOAD
	CaseNumber,
	DateOpened,
	DateClosed
RESIDENT [Cases];

Now load this script up with the calendar script and you can see how it works by playing with a few charts.

…

Ok I am back with a problem.

PROBLEM
If the case is not closed yet, it is not being counted. I need to fix that, because that is not going to work for me. I need to count the cases that are currently open.

So back to research mode…I will update you as I can.

See this forum post:
http://community.qlikview.com/forums/t/23247.aspx

Solved my own question by continuing to RTM.

I needed to add the following just before my Load Statement:

NullAsValue SFCaseDateClosed;